﻿using System;
using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ConsoleApplication1
{
	/// <summary>
	/// Class to export data in Excel file by using OpenXml format. 
	/// </summary>
	internal class ExcelFileExporter
	{
		
		/// <summary>
		/// Fotter message of the Excel sheet.
		/// </summary>
		public String Fotter { get; set; }

		/// <summary>
		/// Method creates new excel document and export data.
		/// </summary>
		/// <param name="dataTable">Exported data.</param>
		/// <param name="excelFilename">Excel document file name.</param>
		/// <returns>Returns <c>true</c> if export finished successfully, otherwise <c>false</c>.</returns>
		public Boolean CreateExcelDocument(DataTable dataTable, string excelFilename)
		{
			try
			{
				using (var document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
				{
					createParts(dataTable, document);
				}

				return true;

			}
			catch (Exception ex)
			{

			}
		}

		private void createParts(DataTable dataTable, SpreadsheetDocument document)
		{
			var workbookPart = document.AddWorkbookPart();
			var workbook = new Workbook();
			workbookPart.Workbook = workbook;

			var sheetName = String.IsNullOrEmpty(dataTable.TableName) ? "CQGSheet" : dataTable.TableName; 

			var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(sheetName);
			writeDataTableToExcelWorksheet(dataTable, worksheetPart);
			var sheet = new Sheet { Name = sheetName, SheetId = 1, Id = sheetName};

			var sheets = new Sheets(sheet);
			workbook.Append(sheets);
		}

		private void writeDataTableToExcelWorksheet(DataTable dataTable, WorksheetPart worksheetPart)
		{
			var numberOfColumns = dataTable.Columns.Count;

			var rowIndex = 1U;
			var sheetData = new SheetData();

			sheetData.Append(writeSheetFotterHeader(Header, ref rowIndex));

			sheetData.Append(writeTableHeader(dataTable, ref rowIndex));

			foreach (DataRow dr in dataTable.Rows)
			{
				var newExcelRow = new Row { RowIndex = rowIndex };

				for (var collumnIndex = 0; collumnIndex < numberOfColumns; collumnIndex++)
				{
					appendTextCell(String.Concat(getExcelColumnName(collumnIndex), rowIndex),
						dr.ItemArray[collumnIndex].ToString(), newExcelRow);
				}
				sheetData.Append(newExcelRow);
				rowIndex++;
			}
			rowIndex++;
			sheetData.Append(writeSheetFotterHeader(Fotter, ref rowIndex));

			worksheetPart.Worksheet = new Worksheet(sheetData);
		}

		private Row writeSheetFotterHeader(String message, ref UInt32 rowIndex)
		{
			if(String.IsNullOrEmpty(message))
			{
				return null;
			}

			var row = new Row { RowIndex = rowIndex };
			appendTextCell(String.Concat(getExcelColumnName(0), rowIndex), message, row);
			rowIndex++;
			return row;
		}

		private static Row writeTableHeader(DataTable table, ref UInt32 rowIndex)
		{
			var row = new Row { RowIndex = rowIndex };
			var numberOfColumns = table.Columns.Count;
			for (var collumnIndex = 0; collumnIndex < numberOfColumns; collumnIndex++)
			{
				var col = table.Columns[collumnIndex];
				appendTextCell(String.Concat(getExcelColumnName(collumnIndex), rowIndex), col.Caption, row);
			}
			rowIndex++;
			return row;
		}

		private static void appendTextCell(String cellReference, String cellStringValue, Row excelRow)
		{
			var cell = new Cell { CellReference = cellReference, DataType = CellValues.String };
			cell.Append(new CellValue { Text = cellStringValue });
			excelRow.Append(cell);
		}

		private static String getExcelColumnName(int columnIndex)
		{
			if (columnIndex < 26)
				return
					((char)('A' + columnIndex)).ToString();

			var firstChar = (char)('A' + (columnIndex / 26) - 1);
			var secondChar = (char)('A' + (columnIndex % 26));

			return string.Format("{0}{1}", firstChar, secondChar);
		}
	}
}
